package com.xlro.dao;

import com.xlro.db.JDBCUtil;
import com.xlro.domain.Book;
import com.xlro.domain.BorrowBooks;
import com.xlro.domain.BrowseBook;
import com.xlro.domain.CollBook;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;

public class BookDao {

//    获取所有图书
    public ArrayList<Book> getAllBook() {
        ArrayList<Book> books = new ArrayList<>();
        try (
                ResultSet resultSet = JDBCUtil.getInstance().executeQueryRS("select * from books", null)){
                    Book book = null;
                    while (resultSet.next()){
                        book = new Book();
                        book.setId(resultSet.getString(1));
                        book.setName(resultSet.getString("name"));
                        book.setAuthor(resultSet.getString(3));
                        book.setLibraryId(resultSet.getString(4));
                        book.setSortId(resultSet.getString(5));
                        book.setPosition(resultSet.getString(6));
                        book.setStatus(resultSet.getString(7));
                        book.setDesc(resultSet.getString(8));

                        books.add(book);
                    }
        } catch(SQLException e){
            e.printStackTrace();
        }

        return books;
    }

//    记录查看历史
    public int insertBrowse(String bookId, String userId, String thisTime) {

        return JDBCUtil.getInstance().executeUpdate("insert into browser_records(browser_records.book_id" +
                ",browser_records.user_id,browser_records.bro_time) VALUES(?,?,?)", new Object[]{bookId,userId,thisTime});
    }

//    借阅图书
    public int insertBorrowBooks(String card_id, String book_id, String borrow_date, String end_date) {

        int returnBorrowBookNum = borrowBookNum(card_id, book_id);


        if(returnBorrowBookNum<2){
            return JDBCUtil.getInstance().executeUpdate("insert into borrow_books(borrow_books.card_id,borrow_books.book_id" +
                    ",borrow_books.borrow_date,borrow_books.end_date) VALUES(?,?,?,?)", new Object[]{card_id,book_id,borrow_date,end_date});
//            return returnBorrowBookNum+1;
        }else{
            return returnBorrowBookNum;
        }


//        return JDBCUtil.getInstance().executeUpdate("insert into borrow_books(borrow_books.card_id,borrow_books.book_id" +
//                ",borrow_books.borrow_date,borrow_books.end_date) VALUES(?,?,?,?)", new Object[]{card_id,book_id,borrow_date,end_date});

    }

//     当前用户所借阅书籍总数目
    public int borrowBookNum(String card_id, String book_id){

//        SELECT * FROM borrow_books WHERE borrow_books.book_id = "1" and borrow_books.card_id = "1"
//        SELECT COUNT(card_id) FROM `borrow_books` WHERE card_id = "1"
        int lines = 0;
        try(
//                and borrow_books.book_id = ?
                ResultSet resultSet = JDBCUtil.getInstance().executeQueryRS("SELECT * FROM borrow_books WHERE borrow_books.card_id =?"
                        , new Object[]{card_id}) ){

            while (resultSet.next()){
                lines++;
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return lines;
    }

    public ArrayList<BrowseBook> selectBrowseBook(String userId) {

        BrowseBook browseBook = null;
        ArrayList<BrowseBook> browseBooks = new ArrayList<>();
        try(
                ResultSet resultSet = JDBCUtil.getInstance().executeQueryRS("SELECT browser_records.id,browser_records.user_id,browser_records.book_id,browser_records.bro_time,books.`name`,books.position,books.`status` FROM `browser_records`,`books` where browser_records.book_id = books.id and browser_records.book_id = ?"
                        , new Object[]{userId}) ) {
            while (resultSet.next()) {
                browseBook = new BrowseBook();
                browseBook.setId(resultSet.getString(1));
                browseBook.setUserId(resultSet.getString(2));
                browseBook.setBookId(resultSet.getString(3));
                browseBook.setDate(resultSet.getString(4));
                browseBook.setBookName(resultSet.getString(5));
                browseBook.setBookPosition(resultSet.getString(6));
                browseBook.setStatus(resultSet.getString(7));
                browseBooks.add(browseBook);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return browseBooks;
    }

    public String alterCollBook(String userId, String bookId, String aThis) {

//        获得收藏书籍，无错误
        ArrayList<CollBook> collBooks = this.collBookNumByBookId(userId);

//        遍历图书
        for (CollBook b :
                collBooks) {

//            数据库找到 用户相同 id -- 删除数据 ， 反之 收藏图书
            if (b.getBooksId().equals(bookId)) {
                int i = JDBCUtil.getInstance().executeUpdate("DELETE FROM book_user_collect " +
                        "WHERE book_user_collect.user_id= ?" +
                        " and book_user_collect.books_id = ?", new Object[]{userId, bookId});
                return i > 0 ? "成功取消收藏图书" + i + "本" : "取消收藏失败" ;
            }
        }
        return JDBCUtil.getInstance().executeUpdate("INSERT INTO book_user_collect(book_user_collect.user_id" +
                ",book_user_collect.books_id" +
                ",book_user_collect.collect_time)" +
                "VALUES(?,?,?)", new Object[]{userId, bookId, aThis})>0?"收藏成功":"收藏失败";
    }

//    查询当前用户所收藏图书
    public ArrayList<CollBook> collBookNumByBookId(String userId){
        //        SELECT  book_user_collect.books_id FROM book_user_collect WHERE book_user_collect.user_id= ?

        CollBook collBook = null;
        ArrayList<CollBook> collBooks = new ArrayList<>();
        try(
                ResultSet resultSet = JDBCUtil.getInstance().executeQueryRS("SELECT *" +
                                " FROM book_user_collect WHERE book_user_collect.user_id=?"
                        , new Object[]{userId}) ) {
            while (resultSet.next()) {
                collBook = new CollBook();
                collBook.setId(resultSet.getString(1));
                collBook.setUserId(resultSet.getString(2));
                collBook.setBooksId(resultSet.getString(3));
                collBook.setCollectTime(resultSet.getString(4));
                collBooks.add(collBook);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return collBooks;

    }

    public String delAllColBook(String userId) {
        int i = JDBCUtil.getInstance().executeUpdate("DELETE FROM browser_records\n" +
                " WHERE browser_records.user_id= ?", new Object[]{userId});
        return i > 0 ? "成功消除" + i + "条浏览记录" : "错误！清空记录失败！" ;

    }

    public ArrayList<CollBook> collBookRecord(String userId) {

        CollBook collBook = null;
        ArrayList<CollBook> collBooks = new ArrayList<>();
        try(
                ResultSet resultSet = JDBCUtil.getInstance().executeQueryRS("SELECT * FROM `book_user_collect` where user_id = ?"
                        , new Object[]{userId}) ) {
            while (resultSet.next()) {
                collBook = new CollBook();
                collBook.setId(resultSet.getString(1));
                collBook.setUserId(resultSet.getString(2));
                collBook.setBooksId(resultSet.getString(3));
                collBook.setCollectTime(resultSet.getString(4));
                collBooks.add(collBook);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return collBooks;
    }

    public ArrayList<BorrowBooks> selectBorrowBook(String userId) {
        BorrowBooks borrowBook = null;
        ArrayList<BorrowBooks> borrowBooks = new ArrayList<>();
        try(
                ResultSet resultSet = JDBCUtil.getInstance().executeQueryRS("SELECT * FROM `borrow_books` WHERE borrow_books.card_id= ?"
                        , new Object[]{userId}) ) {
            while (resultSet.next()) {
                borrowBook = new BorrowBooks();
                borrowBook.setId(resultSet.getString(1));
                borrowBook.setUserId(resultSet.getString(2));
                borrowBook.setBookId(resultSet.getString(3));
                borrowBook.setBorrowDate(resultSet.getString(4));
                borrowBook.setEndDate(resultSet.getString(5));
                borrowBook.setReturnDate(resultSet.getString(6));
                borrowBook.setIllegal(resultSet.getString(7));
                borrowBook.setManagerId(resultSet.getString(8));
                borrowBooks.add(borrowBook);
            }
        }catch (Exception e){
            e.printStackTrace();
        }
        return borrowBooks;
    }

    public int addOneBook(String name, String author, String library, String sort, String position, String status, String desc) {
        return JDBCUtil.getInstance().executeUpdate("insert into books(books.`name`,books.`author·,books.`library_id`" +
                ",  books.`sort_id`,books.`position`,books.`status`,books.`description`)VALUES(?,?,?,?,?,?,?)"
                , new Object[]{name,author,library,sort,position,status,desc});
    }

    public int deleteBookById(String id) {
        return JDBCUtil.getInstance().executeUpdate("DELETE FROM `books` WHERE id = ?", new Object[]{id});
    }

    public int updateBookById(String id, String name, String author, String library, String sort, String position, String status, String desc) {
        return JDBCUtil.getInstance().executeUpdate("UPDATE books set books.`name` = ?,author=?,books.library_id=?" +
                ",sort_id=? ,position=?,books.`status`=?,description=? WHERE id=?", new Object[]{name,author,library,sort,position,status,desc,id});
    }
}
